IF EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[cms_sp_PageSelectAllByPageKey]')
              AND TYPE IN (N'P' ,N'PC')
   )
    DROP PROCEDURE [dbo].[cms_sp_PageSelectAllByPageKey]
GO
CREATE PROCEDURE [dbo].[cms_sp_PageSelectAllByPageKey]
(
    @SiteId                 INT
   ,@SiteGroupId            INT
   ,@IsInAdministration     BIT
   ,@PageKey                NVARCHAR(255)
)
AS
	SELECT Pages.PageId
	      ,Pages.PublicPageId
	      ,Pages.SiteGroupId
	      ,Pages.SiteId
	      ,Pages.SystemLanguageId
	      ,Pages.PageTypeKey
	      ,Pages.PageBehaviourTypeKey
	      ,Pages.PageNodeId.GetLevel() AS PageLevel
	      ,Pages.PageName
	      ,Pages.PageUrl
	      ,Pages.IsVisible
	      ,Pages.UseFileCache
	      ,Pages.PageTitle
	      ,Pages.PageTitleOverride
	      ,Pages.PageDescription
	      ,Pages.PageKeywords
	      ,Pages.CreatedBy
	      ,Pages.DateCreated
	      ,Pages.LastUpdatedBy
	      ,Pages.DateLastUpdated
	      ,Pages.PageKey
	FROM   [dbo].[Pages]
	       INNER JOIN [dbo].[EntityRevisions]
	            ON  EntityRevisions.EntityId = Pages.PageId
	                AND EntityRevisions.EntityTypeParameter = 'Page'
	WHERE  (
	           ([SiteGroupId] IS NULL AND [SiteId] IS NULL)
	           OR ([SiteGroupId] = @SiteGroupId AND [SiteId] IS NULL)
	           OR ([SiteGroupId] = @SiteGroupId AND [SiteId] = @SiteId)
	       )
	       AND (
	               (
	                   @IsInAdministration = 1
	                   AND EntityRevisions.IsLatestRevision = 1
	               )
	               OR (
	                      @IsInAdministration = 0
	                      AND EntityRevisions.IsLatestCompletedRevision = 1
	                  )
	           )
	       AND (
	               (@IsInAdministration = 1)
	               OR (
	                      @IsInAdministration = 0
	                      AND EntityRevisions.IsPublished = 1
	                  )
	           )
	       AND EntityRevisions.IsDeleted = 0
	       AND Pages.PageKey = @PageKey
GO